﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Diagnostics;
using XBMC.Cache.Manager.Shared;
using System.IO;

namespace XBMC.Cache.Manager
{
    public class DatabaseHelper
    {
        private string m_ConnectionString;

        public DatabaseHelper(bool useVideoConnection)
        {
            if (useVideoConnection)
                m_ConnectionString = XBMCVideoConnectionString;
            else
                m_ConnectionString = XBMCMusicConnectionString;
        }

        public string LoadSQLStatement(string statementName)
        {
            string sqlStatement = string.Empty;

            string namespacePart = this.GetType().Namespace;
            string resourceName = namespacePart + "." + statementName;

            using (Stream stm = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
            {
                if (stm != null)
                {
                    sqlStatement = new StreamReader(stm).ReadToEnd();
                }
            }

            return sqlStatement;
        }
        
        public List<T> Read<T>(string sql)
            where T : new()
        {
            List<T> results = new List<T>();
            try
            {
                using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
                {
                    connection.Open();

                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        command.CommandType = CommandType.Text;

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            results = MapDataToBusinessEntityCollection<T>(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.ToString());
            }

            return results;
        }

        public void Execute(string sql)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
                {
                    connection.Open();

                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        command.CommandType = CommandType.Text;
                        int rows = command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.ToString());
            }
        }

        private List<T> MapDataToBusinessEntityCollection<T>(IDataReader dr)
           where T : new()
        {
            Type businessEntityType = typeof(T);
            List<T> entitys = new List<T>();
            Hashtable hashtable = new Hashtable();
            PropertyInfo[] properties = businessEntityType.GetProperties();

            try
            {
                foreach (PropertyInfo info in properties)
                {
                    // Read the custom attributes
                    Attribute[] attrs = Attribute.GetCustomAttributes(info);

                    // See if there is a DB field attribute and if so use it otherwise use the property name
                    Attribute attribute = attrs.FirstOrDefault(attr => attr is DBFieldAttribute);

                    if (attribute == null)
                        hashtable[info.Name.ToUpper()] = info;
                    else
                        hashtable[((DBFieldAttribute)attribute).DBFieldName.ToUpper()] = info;
                }

                // Read the records in the datareader and map them to properties
                while (dr.Read())
                {
                    T newObject = new T();

                    for (int index = 0; index < dr.FieldCount; index++)
                    {
                        PropertyInfo info = (PropertyInfo)hashtable[dr.GetName(index).ToUpper()];

                        if ((info != null) && info.CanWrite)
                        {
                            if (!dr.IsDBNull(index))
                                info.SetValue(newObject, dr.GetValue(index), null);
                        }
                    }

                    entitys.Add(newObject);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                EventLogHelper.WriteEvent(ex.ToString(), EventLogEntryType.Information);
            }

            return entitys;
        }
        
        private string XBMCVideoConnectionString
        {
            get
            {
                // MySQL Connector/NET
                string connect = String.Format("Data Source={0};", RegistryHelper.LoadRegistryOption("DBServer"));
                connect += String.Format("Initial Catalog={0};", RegistryHelper.LoadRegistryOption("DBVideoDBName"));
                connect += String.Format("User ID={0};", RegistryHelper.LoadRegistryOption("DBUsername"));
                connect += String.Format("Password={0};", RegistryHelper.LoadRegistryOption("DBPassword"));
                
                return connect;
            }
        }

        private string XBMCMusicConnectionString
        {
            get
            {
                // MySQL Connector/NET
                string connect = String.Format("Data Source={0};", RegistryHelper.LoadRegistryOption("DBServer"));
                connect += String.Format("Initial Catalog={0};", RegistryHelper.LoadRegistryOption("DBMusicDBName"));
                connect += String.Format("User ID={0};", RegistryHelper.LoadRegistryOption("DBUsername"));
                connect += String.Format("Password={0};", RegistryHelper.LoadRegistryOption("DBPassword"));

                return connect;
            }
        }
    }
}
